﻿using apitest.Application.Entities.Pms;
using MiniExcelLibs;
using Newtonsoft.Json;

namespace apitest.Application.Test.Services
{
    [DynamicApiController]
    public class Test5Service
    {
        ISqlSugarClient _db;
        public Test5Service(ISqlSugarClient db)
        {
            _db = db;
        }

        public async Task<dynamic> T1()
        {
            return await _db.Queryable<HR_User>().Take(10).ToListAsync();
        }

        public async Task<dynamic> T2()
        {
            var date = DateTime.Parse("2024-12-01");
            var days = (date.AddMonths(1) - date).Days;
            var dateList = Enumerable.Range(1, days)
                .Select(it => Convert.ToDateTime(date.ToString("yyyy-MM-" + it)))
                .ToList();

            var res = await _db.Reportable(dateList).ToQueryable<DateTime>()
                .LeftJoin<TestRequestRecord>((t1, t2) => t2.StartDate.Value.ToString("yyyy-MM-dd") == t1.ColumnName.ToString("yyyy-MM-dd"))
                .GroupBy((t1, t2) => t1.ColumnName)
                .Select((t1, t2) => new
                {
                    date = t1.ColumnName.ToString("yyyy-MM-dd"),
                    count = SqlFunc.AggregateCount(t2.Id)
                }).ToListAsync();

            var res1 = await _db.Queryable<TestRequestRecord>()
                .Where(o => o.StartDate.Value.Year == 2024)
                 .GroupBy(o => SqlFunc.DateValue(o.StartDate.Value, DateType.Quarter))
                 .Select(o => new
                 {
                     qyarter = SqlFunc.DateValue(o.StartDate.Value, DateType.Quarter),
                     count = SqlFunc.AggregateCount(o.Id),
                 })
                .ToListAsync();
            return res1;
            return res;
        }

        public async Task<dynamic> T3()
        {
            var queryableLeft = _db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable<DateTime>(); //生成月份
                                                                                                               //ReportableDateType.MonthsInLast1yea 表式近一年月份 并且queryable之后还能在where过滤
            var queryableRight = _db.Queryable<TestRequestRecord>(); //声名表

            //月份和表JOIN
            var list = queryableLeft
               .LeftJoin(queryableRight, (x1, x2) => x2.StartDate.Value.ToString("yyyy-MM") == x1.ColumnName.ToString("yyyy-MM"))
               .GroupBy((x1, x2) => x1.ColumnName)
               .Select((x1, x2) => new
               {
                   //null的数据要为0所以不能用count
                   count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.Id > 0, 1, 0)),
                   date = x1.ColumnName.ToString("yyyy-MM")
               }
                             ).ToList();
            return list;
        }

        public async Task<dynamic> T111()
        {
            var dateList = new List<DateTime>();
            var start = DateTime.Parse("2024-09-15");
            var end = DateTime.Parse("2024-11-1");
            while (start < end)
            {
                dateList.Add(start);
                start = start.AddDays(1);
            }
            var temp = MiniExcel.Query("./123.xlsx", useHeaderRow: true).ToList();
            var list = JsonConvert.DeserializeObject<List<Test5_class>>(JsonConvert.SerializeObject(temp));
            var res = new List<Test5_class>();
            foreach (var day in dateList)
            {
                foreach (var item in list)
                {
                    var data = JsonConvert.DeserializeObject<Test5_class>(JsonConvert.SerializeObject(item));
                    data.批号 = $"{day.ToString("yyyyMMdd")}-{item.批号.Split("-")[1]}";
                    data.接收时间 = day.ToShortDateString();
                    res.Add(data);
                }
            }
            var path = "./111.xlsx";
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            MiniExcel.SaveAs(path, res);
            return "res";
        }

        public async Task<dynamic> T222()
        {
            var tempDateList = new List<string>() {
                "2024-11-5",
                "2024-11-12",
                "2024-11-19",
                "2024-11-26",
            };
            var dateList = new List<DateTime>();
            foreach (var item in tempDateList)
            {
                dateList.Add(DateTime.Parse(item));
            }
            var temp = MiniExcel.Query("./123.xlsx", useHeaderRow: true).ToList();
            var list = JsonConvert.DeserializeObject<List<Test5_class>>(JsonConvert.SerializeObject(temp));
            var res = new List<Test5_class>();
            var numList = new List<string>() {
            "1","2","3","4","5","6","7"
            };
            foreach (var day in dateList)
            {
                foreach (var item in list)
                {
                    var num = item.批号.Split("-")[1];
                    if (numList.Contains(num))
                    {
                        var data = JsonConvert.DeserializeObject<Test5_class>(JsonConvert.SerializeObject(item));
                        data.批号 = $"{day.ToString("yyyyMMdd")}-{num}";
                        data.接收时间 = day.ToShortDateString();
                        res.Add(data);
                    }
                }
            }
            var path = "./222.xlsx";
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            MiniExcel.SaveAs(path, res);
            return "res";
        }

        public async Task<dynamic> T333()
        {
            var str = "[{'name':'HPLC-有关物质(研发)','price':42.3,'hour':40,'group':'HPLC','des':'HPLC','_XID':'row_4061'},{'name':'HPLC-纯度(研发)','price':42.3,'hour':40,'group':'HPLC','des':'HPLC','_XID':'row_4062'},{'name':'HPLC-含量(研发)','price':42.3,'hour':40,'group':'HPLC','des':'HPLC','_XID':'row_4063'},{'name':'GC-残留溶剂(研发)','price':50.9,'hour':40,'group':'GC','des':'GC','_XID':'row_4064'},{'name':'GC-异构体(研发)','price':50.9,'hour':40,'group':'GC','des':'GC','_XID':'row_4065'},{'name':'HPLC-异构体(研发)','price':42.3,'hour':40,'group':'HPLC','des':'HPLC','_XID':'row_4066'},{'name':'HPLC-有关物质1(QC)','price':0,'hour':240,'group':'HPLC','des':'操作+记录+图谱处理','_XID':'row_4067'},{'name':'HPLC-有关物质2(QC)','price':0,'hour':240,'group':'HPLC','des':null,'_XID':'row_4068'},{'name':'HPLC-有关物质3(QC）','price':0,'hour':240,'group':'HPLC','des':null,'_XID':'row_4069'},{'name':'HPLC-纯度(QC)','price':0,'hour':240,'group':'HPLC','des':'操作+记录+图谱处理','_XID':'row_4070'},{'name':'HPLC-含量(QC)','price':0,'hour':240,'group':'HPLC','des':'操作+记录+图谱处理','_XID':'row_4071'},{'name':'GC-残留溶剂1(QC)','price':0,'hour':360,'group':'GC','des':'操作+记录+图谱处理','_XID':'row_4072'},{'name':'GC-异构体(QC)','price':0,'hour':240,'group':'GC','des':'操作+记录+图谱处理','_XID':'row_4073'},{'name':'GC-顶空残留溶剂(QC)','price':0,'hour':360,'group':'GC','des':'操作+记录+图谱处理','_XID':'row_4074'},{'name':'HPLC-异构体(QC)','price':0,'hour':240,'group':'HPLC','des':'操作+记录+图谱处理','_XID':'row_4075'},{'name':'UV-吸光度','price':0,'hour':240,'group':'UV','des':null,'_XID':'row_4076'},{'name':'流动相配制+平衡','price':0,'hour':120,'group':'HPLC','des':'配制+平衡','_XID':'row_4077'},{'name':'试液配制','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4078'},{'name':'外观','price':0,'hour':20,'group':'理化','des':null,'_XID':'row_4079'},{'name':'引湿性','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4080'},{'name':'水分标定','price':0,'hour':30,'group':'理化','des':null,'_XID':'row_4081'},{'name':'水分测定(KF)','price':0,'hour':30,'group':'理化','des':null,'_XID':'row_4082'},{'name':'溶液的澄清度与颜色','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4083'},{'name':'炽灼残渣','price':0,'hour':90,'group':'理化','des':'恒重+实验+记录','_XID':'row_4084'},{'name':'理化鉴别','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4085'},{'name':'溶解度','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4086'},{'name':'干燥失重','price':0,'hour':90,'group':'理化','des':'恒重+实验+记录','_XID':'row_4087'},{'name':'红外鉴别','price':0,'hour':120,'group':'仪器','des':null,'_XID':'row_4088'},{'name':'紫外鉴别','price':0,'hour':90,'group':'仪器','des':null,'_XID':'row_4089'},{'name':'重金属检测','price':0,'hour':90,'group':'理化','des':null,'_XID':'row_4090'},{'name':'硫酸盐','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4091'},{'name':'氯化物','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4092'},{'name':'铵盐','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4093'},{'name':'砷盐','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4094'},{'name':'手动滴定','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4095'},{'name':'电位滴定','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4096'},{'name':'酸度','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4097'},{'name':'比旋度','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4098'},{'name':'菌种传代','price':0,'hour':210,'group':'微生物','des':null,'_XID':'row_4099'},{'name':'微生物限度','price':0,'hour':300,'group':'微生物','des':null,'_XID':'row_4100'},{'name':'细菌内毒素','price':0,'hour':90,'group':'微生物','des':null,'_XID':'row_4101'},{'name':'培养基适应性检查','price':0,'hour':240,'group':'微生物','des':null,'_XID':'row_4102'},{'name':'环境监测','price':0,'hour':180,'group':'微生物','des':null,'_XID':'row_4103'},{'name':'取样','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4104'},{'name':'留样','price':0,'hour':30,'group':'综合','des':null,'_XID':'row_4105'},{'name':'产品报告','price':0,'hour':50,'group':'综合','des':null,'_XID':'row_4106'},{'name':'原料报告','price':0,'hour':40,'group':'综合','des':null,'_XID':'row_4107'},{'name':'GC-含量（QC）','price':0,'hour':240,'group':'GC','des':null,'_XID':'row_4108'},{'name':'鉴别（HPLC）','price':0,'hour':10,'group':'HPLC','des':null,'_XID':'row_4109'},{'name':'鉴别(GC)','price':0,'hour':10,'group':'GC','des':null,'_XID':'row_4110'},{'name':'色度','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4111'},{'name':'钙盐','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4112'},{'name':'硼丰度','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4113'},{'name':'含量（理化）','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4114'},{'name':'游离酸（理化）','price':0,'hour':120,'group':'理化','des':null,'_XID':'row_4115'},{'name':'熔点','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4116'},{'name':'GC-残留溶剂2(QC)','price':0,'hour':360,'group':'GC','des':null,'_XID':'row_4117'},{'name':'GC-残留溶剂3(QC)','price':0,'hour':360,'group':'GC','des':null,'_XID':'row_4118'},{'name':'GC-残留溶剂4(QC)','price':0,'hour':360,'group':'GC','des':null,'_XID':'row_4119'},{'name':'酸碱度','price':0,'hour':60,'group':'理化','des':null,'_XID':'row_4120'},{'name':'硼10酸','price':0,'hour':0,'group':'理化','des':null,'_XID':'row_4121'},{'name':'卤化物和卤素','price':0,'hour':0,'group':'理化','des':null,'_XID':'row_4122'},{'name':'碘酸盐','price':0,'hour':0,'group':'理化','des':null,'_XID':'row_4123'},{'name':'HPLC-有关物质4(QC）','price':0,'hour':0,'group':'HPLC','des':null,'_XID':'row_4124'},{'name':'控制菌（大肠埃希菌）','price':0,'hour':0,'group':'微生物','des':null,'_XID':'row_4125'},{'name':'控制菌（沙门菌）','price':0,'hour':0,'group':'微生物','des':null,'_XID':'row_4126'},{'name':'控制菌（金黄色葡萄球菌）','price':0,'hour':0,'group':'微生物','des':null,'_XID':'row_4127'},{'name':'控制菌（白色念珠菌）','price':0,'hour':0,'group':'微生物','des':null,'_XID':'row_4128'},{'name':'控制菌（铜绿假单胞菌）','price':0,'hour':0,'group':'微生物','des':null,'_XID':'row_4129'},{'name':'GC-纯度（QC）','price':0,'hour':0,'group':'GC','des':null,'_XID':'row_4130'},{'name':'GC-纯度（研发）','price':0,'hour':0,'group':'GC','des':null,'_XID':'row_4131'},{'name':'GC-含量（研发）','price':0,'hour':0,'group':'GC ','des':null,'_XID':'row_4132'},{'name':'滴定-含量','price':0,'hour':0,'group':'理化','des':null,'_XID':'row_4133'},{'name':'水分-快干','price':0,'hour':0,'group':'理化','des':null,'_XID':'row_4134'}]";
            var res = JsonConvert.DeserializeObject<List<dict_class>>(str);

            var path = "./333.xlsx";
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            MiniExcel.SaveAs(path, res);
            return "res";
        }

        public async Task<dynamic> T666()
        {
            var allLogIds = await _db.Queryable<QC_Refer_Log>()
               .Select(o => o.ReferId)
               .ToListAsync();
            var allList = await _db.Queryable<QC_Refer>()
                .Where(o => !string.IsNullOrWhiteSpace(o.Batch) && !allLogIds.Contains(o.Id))
                .ToListAsync();
            var list = new List<QC_Refer_Log>();
            foreach (var item in allList)
            {
                var d = new QC_Refer_Log()
                {
                    Batch = item.Batch,
                    ReferId = item.Id,
                    Calibration = item.Calibration,
                    Purity = item.Purity,
                    Content = item.Content,
                    ReportNo = item.ReportNo,
                    ReportTime = item.ReportTime,
                    RemarkTime = item.RemarkTime,
                    Remark = "1314",
                    CreateUserName = "周美",
                    CreateTime = item.ReportTime
                };
                list.Add(d);
            }
            await _db.Insertable(list).ExecuteCommandAsync();
            return allList.Count();
        }
        public async Task<dynamic> T122()
        {
            //var list = await _db.Queryable<GuangAn_TestRecordDetail>()
            //    .LeftJoin<GuangAn_TestRecord>((t1, t2) => t1.RecordId == t2.Id)
            //    .Where((t1,t2)=>t1.RecordId==SqlFunc.Subqueryable<GuangAn_TestRecordDetail>()
            //    .Where(o=>o.RecordId<1230).Select(o=>o.RecordId))
            //    .Take(10)
            //    .ToListAsync();
            var list1 = await _db.Queryable<GuangAn_TestRecordDetail>()
               .LeftJoin<GuangAn_TestRecord>((t1, t2) => t1.RecordId == t2.Id)
               .Where((t1, t2) => SqlFunc.Subqueryable<GuangAn_TestRecordDetail>()
               .Where(o => o.RecordId < 1230).Any())
               .Take(30)
               .ToListAsync();
            return list1;
        }

        public async Task<dynamic> t88888()
        {
            var val = await _db.Queryable<T_Dict>().FirstAsync(o => o.Key == "ga_testcategory");
            var categoryList = JsonConvert.DeserializeObject<List<ga_testtypeClass>>(val.Value.ToString());
            var list = await _db.Queryable<GuangAn_TestRecordDetail>()
                .Where(o => o.Status == "已完成"
                && SqlFunc.Between(o.EndTime.Value, DateTime.Parse("2025-01-01"), DateTime.Parse("2025-07-01"))
                && o.Instrument != "无仪器")
                .GroupBy(o => new { o.Instrument, o.Category })
                .Select(o => new
                {
                    o.Instrument,
                    o.Category,
                    count = SqlFunc.AggregateCount(o.Instrument)
                })
                .OrderBy(o => o.Instrument)
                .ToListAsync();
            var res = new List<dynamic>();
            var devices = list.Select(o => o.Instrument).Distinct().ToList();
            foreach (var device in devices)
            {
                var _list = list.Where(o => o.Instrument == device);
                double hour = 0;
                foreach (var item in _list)
                {
                    var category = categoryList.FirstOrDefault(o => o.name == item.Category);
                    if (category != null)
                    {
                        hour += category.hour * item.count;
                    }
                }
                res.Add(new
                {
                    name = device,
                    hour = hour,
                });
            }
            MiniExcel.SaveAs("1111a.xlsx", res);
            return res;
        }

        public class dict_class
        {
            public string name { get; set; }
            public double price { get; set; }
            public int hour { get; set; }
            public string group { get; set; }
            public string des { get; set; }
        }
        public class Test5_class
        {
            public string 仪器编号 { get; set; }
            public string 送检人 { get; set; }
            public string 送检部门 { get; set; }
            public string 样品类型 { get; set; }
            public string 项目编号 { get; set; }
            public string 批号 { get; set; }
            public string CAT { get; set; }
            public string 分析室 { get; set; }
            public string 检测项目 { get; set; }
            public string 接收时间 { get; set; }
            public string 检测人员 { get; set; }
            public string 样品来源 { get; set; }
            public string 工时 { get; set; }
        }
    }
}
